﻿using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Globalization;
using System.Linq;
using System.Text;
using BE;
using System.Configuration;

namespace BLL
{
    public class languageConnector
    {
        
        public static IEnumerable<Language> getAllLanguages(CultureInfo c1, CultureInfo c2)
        {

            return getAllLanguages(c1.Name, c2.Name);
        }
        public static IEnumerable<Language> getAllLanguages(string c1, string c2)
        {

            DataContext db = DBCommands.GetConnection();
            IEnumerable<Language> v = from l in db.GetTable<Language>()
                                      where l.LanguageCode == c1 || l.LanguageCode == c2
                                      orderby l.TagId
                                      select l;
            return v;
        }
        public static IEnumerable<Language> getAllLanguages(string c1)
        {

            DataContext db = DBCommands.GetConnection();
            IEnumerable<Language> v = from l in db.GetTable<Language>()
                                      where l.LanguageCode == c1
                                      orderby l.TagId
                                      select l;
            return v;
        }
        public static void createDB(LanguageTable db)
        {

            db.CreateDatabase();
        }

        public static void createTablesIfNotExists(CultureInfo DefaultValue)
        {
            if (!IsTablesInDb())
            {
                DataContext db = DBCommands.GetConnection();
                string tables;
                #region tables (big sql script)
                tables = "SET ANSI_NULLS ON "
                + "; "
                + "SET QUOTED_IDENTIFIER ON "
                + "; "
                + "CREATE TABLE [TagsChanged]( "
                + "	[Tag] [int] NOT NULL "
                + ") "
                + "; "
                + ""
                + "SET ANSI_NULLS ON "
                + "; "
                + "SET QUOTED_IDENTIFIER ON "
                + "; "
                + "CREATE TABLE [PromptedControls]( "
                + "[Name] [nvarchar](200) NOT NULL, "
                + "[use] [bit] NOT NULL "
                + ") "
                + "; "
                + ""
                + "SET ANSI_NULLS ON "
                + "; "
                + "SET QUOTED_IDENTIFIER ON "
                + "; "
                + "CREATE TABLE [LanguageStrings]( "
                + "	[Id] [int] IDENTITY(1,1) NOT NULL, "
                + "	[TagId] [int] NOT NULL, "
                + "	[LanguageCode] [nvarchar](20) NOT NULL, "
                + "	[Text] [nvarchar](100) NOT NULL, "
                + "	[MasterControl] [nvarchar](200) NULL, "
                + "CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED  "
                + "( "
                + "	[Id] ASC "
                + ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)) "
                + ""
                + "; "
                + ""
                + "ALTER TABLE [LanguageStrings] ADD  CONSTRAINT [DF_Language_LanguageCode]  DEFAULT (N'" + DefaultValue.Name + "') FOR [LanguageCode] "
                + "; ";
                #endregion
                db.ExecuteCommand(tables);
            }
        }

        public static Boolean IsTablesInDb() //checker om tabellerne der skal bruges af oversætteren eksisterer
        {
            DataContext db = DBCommands.GetConnection();
            int LS = db.ExecuteQuery<int>("if OBJECT_ID('LanguageStrings') is Not NULL BEGIN SELECT 1 END else begin SELECT 0 end").FirstOrDefault();
            int PS = db.ExecuteQuery<int>("if OBJECT_ID('PromptedControls') is Not NULL BEGIN SELECT 1 END else begin SELECT 0 end").FirstOrDefault();
            int TS = db.ExecuteQuery<int>("if OBJECT_ID('TagsChanged') is Not NULL BEGIN SELECT 1 END else begin SELECT 0 end").FirstOrDefault();
            return LS == 1 && PS == 1 && TS == 1;
        }

        public static void SendListToDb(Dictionary<string, ControlInfo> texts,CultureInfo info) //når oversætteren har fundet tekster og skal gemme dem til databasen
        {
            
            
            DataContext db = DBCommands.GetConnection();
            
            foreach (ControlInfo s in texts.Values)
            {
                if (s.AddToDb) // I nogen tilfælde skal teksten ikke tilføjes men ligger i text dictionarien af andre årsager
                {
                    Language l = new Language();
                    l.Text = s.Text;
                    l.TagId = s.Tag;
                    l.MasterControl = s.FullName;
                    l.LanguageCode = info.Name; // selvom denne kolonne indeholder en default værdi er linq dårlig til at håndtere default værdier
                    db.GetTable<Language>().InsertOnSubmit(l);
                }
            }
           
            db.SubmitChanges();
        }

       
        public static List<Language> getLanguagesByDefaultLanguage()
        {
            CultureInfo DefaultLanguage = getDefaultLanguage();

            DataContext db = DBCommands.GetConnection();
            List<Language> list = (from l in db.GetTable<Language>()
                                   where l.LanguageCode == DefaultLanguage.Name
                                   select l).ToList<Language>();
            return list;

        }

        public static CultureInfo getDefaultLanguage()//få fat i default værdien fra language column i LanguageStrings tabellen
        {
            DataContext db = DBCommands.GetConnection();
            string result = db.ExecuteQuery<string>("SELECT object_definition(default_object_id) AS definition "
            + "FROM   sys.columns "
            + "WHERE  name      = 'LanguageCode' "
            + "AND    object_id = object_id('LanguageStrings')").FirstOrDefault();
            int startIdx = result.IndexOf("'")+1;
            int endIdx = result.IndexOf("'", startIdx);
            return new CultureInfo(result.Substring(startIdx,endIdx-startIdx));
            

        }

        public static Dictionary<int, string> getTextsByLanguage(string lan) //returner alle tekster i parameterens sprog
        {
            DataContext db = DBCommands.GetConnection();
            Dictionary<int, string> di = new Dictionary<int, string>();
            IQueryable<Language> s = from l in db.GetTable<Language>()
                                     where l.LanguageCode == lan
                                     select l;
            foreach (Language x in s)
            {
                di.Add(x.TagId, x.Text);
            }
            return di;
        }

        public static void TransferMaster(List<int> NewTagMasters, Dictionary<int, List<ControlInfo>> NameSlaves) // hvis en text skal have ny master sker magien her
        {
            DataContext db = DBCommands.GetConnection();
            foreach (int tagid in NewTagMasters)
            {
                if (NameSlaves.ContainsKey(tagid) &&  NameSlaves[tagid].Count > 0)
                {
                    db.ExecuteCommand("UPDATE LanguageStrings set MasterControl = {0} where TagId={1}"  ,NameSlaves[tagid][0].FullName,tagid); // fordi Linq ikke understøtter batch opdateringer bruges der sql da teksten kan have oversættelser og deres oversættelser også skal indholde masterteksten
                }
                else
                {
                    db.ExecuteCommand("UPDATE LanguageStrings set MasterControl = '' where TagId={0}" , tagid);
                }
            }
        }

        public static void ChangeTextOnTags(Dictionary<int, ControlInfo> tagChanges, CultureInfo CurrentCulture) //skift teksten på et tag
        {
            DataContext db = DBCommands.GetConnection();
            foreach (ControlInfo CI in tagChanges.Values)
            {

                db.ExecuteCommand("UPDATE LanguageStrings set Text = {0} where TagId={1} and LanguageCode={2}", CI.Text, CI.Tag.ToString(), CurrentCulture.Name);
                db.ExecuteCommand("INSERT INTO TagsChanged (Tag) VALUES ({0})", CI.Tag); //insertet laves som sql da linq ikke kan indsætte i en tabel uden en primær nøgle
            }
        }

        
        //gemmer brugerens valg af lister der skal gemmes tekster fra og hvilke der skal ignoreres
        public static void RememberLists(List<string> removeList, Dictionary<int, Dictionary<int, ControlInfo>> saveList)
        {
            DataContext db = DBCommands.GetConnection();
            
            foreach (Dictionary<int, ControlInfo> cList in saveList.Values)
            {
                
                string value = cList.First().Value.parentControl;
                db.ExecuteCommand("INSERT INTO PromptedControls (Name, [use] ) VALUES({0} ,1 )", value);  //insertet laves som sql da linq ikke kan indsætte i en tabel uden en primær nøgle
            } 
            foreach (string parent in removeList)
            {
                
                string value = parent;
                db.ExecuteCommand("INSERT INTO PromptedControls (Name, [use] ) VALUES({0} ,0 )", value);  //insertet laves som sql da linq ikke kan indsætte i en tabel uden en primær nøgle
            }
            
   
        }

        //returner listen over nogle kontroller som ikke skal vedligeholdes af programmet
        public static Dictionary<string, bool> getSaveList()
        {
            DataContext db = DBCommands.GetConnection();
            Dictionary<string, bool> di = new Dictionary<string, bool>();
            IQueryable<PromptedControls> list = from l in db.GetTable<PromptedControls>()
                                           select l;
            foreach (PromptedControls c in list)
            {
                di.Add(c.name, c.use);
            }
            return di;
        }

        public static string getText(int tag, string p)
        {
            DataContext db = DBCommands.GetConnection();
            string text = (from l in db.GetTable<Language>()
                           where l.TagId == tag && l.LanguageCode == p
                           select l.Text).FirstOrDefault();
            return text;
        }

        public static void edit(Language language)
        {
            DataContext db = DBCommands.GetConnection();
            Language editThis = (from l in db.GetTable<Language>()
                           where l.Id == language.Id
                           select l).FirstOrDefault<Language>();
            
            editThis.Text = language.Text;
            db.SubmitChanges();
        }

        public static void addLanguage(Language language)
        {
            DataContext db = DBCommands.GetConnection();
            db.GetTable<Language>().InsertOnSubmit(language);
            db.SubmitChanges();
        }

        public static void addLanguages(List<Language> language)
        {
            DataContext db = DBCommands.GetConnection();
            db.GetTable<Language>().InsertAllOnSubmit(language);
            db.SubmitChanges();
        }

        //returner værdier hvor taggets tekst er ændret, oversættelserne bliver derfor også smidt med så de kan passe med originalteksten
        public static IEnumerable<Language> getChangedLanguages()
        {
            DataContext db = DBCommands.GetConnection();
            return from l in db.GetTable<Language>()
                   join t in db.GetTable<TagsChanged>()
                   on l.TagId equals t.Tag
                   select l;
        }

        //tags der har haft deres tekst ændret er ændret og denne funktion kaldes til at fjerne alle numre fra tagschanged tabellen for at notere at alle oversættelser passer med deres original
        public static void TextsChanged()
        {
            DataContext db = DBCommands.GetConnection();
            db.ExecuteCommand("delete from TagsChanged");
        }
    }
}
